using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
using MySql.Data.MySqlClient;
using LdExpress;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Configuration;
using System.Collections;
namespace LdExpress
{
    public class UsersPermissions
    {
        public long ID = 0;
        public int UserID = 0;
        public int ObjectID = 0;
        public int Value = 0;
        public UsersPermissions()
        {
        }

        public UsersPermissions(long lID)
        {
            this.ID = lID;
        }
        public void Save(MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            string strTableName = "tbl_permissions";

            if (ID <= 0) // new object, we insert new record to database
            {
                //ID = long.Parse(DBModule.GetNewID(typeof(Bill), "tbl_bill" , "ID", "ID", cn, trans));
                strSQL = "INSERT INTO tbl_permissions(" +
                "UserID," +
                "ObjectID," +
                "Value" +
                ")" +
                "VALUES(" +
                UserID.ToString() + "," +
                ObjectID.ToString() + "," +
                Value.ToString() +
                ");";

                // run SQL statement
                //throw new Exception(strSQL);
                DBModule.ExecuteNonQuery(strSQL, cn, trans);
                UserObjectsLog.SaveObjectActionLog("UsersPermissions", "add", cn, trans);
                //throw new Exception(strSQL);
                //GV.Response.Write(strSQL);
            }
            else // edit object, we update old record in database
            {
                // build SQL statement
                strSQL = "Update " + strTableName + " set " +
                             " UserID=" + UserID.ToString() +
                             " ,ObjectID=" + ObjectID.ToString() +
                             " ,Value=" + Value.ToString() +
                    "  where ID=" + ID.ToString();
                // run SQL statement
                DBModule.ExecuteNonQuery(strSQL, cn, trans);
                UserObjectsLog.SaveObjectActionLog("UsersPermissions", "edit", cn, trans);
                //GV.Response.Write(strSQL);
            }
            //throw new Exception(strSQL);
        }
        public void Delete(MySqlConnection cn, MySqlTransaction trans)
        {

            string strSQL = "DELETE FROM tbl_permissions WHERE ID = " + this.ID.ToString();
            UserObjectsLog.SaveObjectActionLog("UsersPermissions", "delete", cn, trans);
            //GV.Response.Write("sqldelete=" + strSQL);
            DBModule.ExecuteNonQuery(strSQL, cn, trans);
        }
        public void Delete(string UserID, MySqlConnection cn, MySqlTransaction trans)
        {

            string strSQL = "DELETE FROM tbl_permissions WHERE UserID = " + UserID;

            //GV.Response.Write("sqldelete=" + strSQL);
            DBModule.ExecuteNonQuery(strSQL, cn, trans);
        }
        public void Load(MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";


            strSQL = "SELECT * FROM tbl_permissions WHERE ID = " + ID.ToString() + " limit 0,1";


            DataSet ds = DBModule.ExecuteQuery(strSQL, cn, trans);
            //Fill data into this object
            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow dr = ds.Tables[0].Rows[0];
                if (!dr.IsNull("ID"))
                    ID = long.Parse(dr["ID"].ToString());
                if (!dr.IsNull("UserID"))
                    UserID = int.Parse(dr["UserID"].ToString());
                if (!dr.IsNull("ObjectID"))
                    ObjectID = int.Parse(dr["ObjectID"].ToString());
                if (!dr.IsNull("Value"))
                    Value = int.Parse(dr["Value"].ToString());
            }
        }
        public int Login(string UserID, string Passwords, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            int retID = 0;
            try
            {
                strSQL = " Select ID from tbl_permissions where UserID=" + UserID;// +"' and Status=1";
                DataSet ds = DBModule.ExecuteQuery(strSQL, cn, trans);
                if (ds != null)
                {
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        retID = int.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                    }
                }
                return retID;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        public void GetList(string strIDs, string UserID, string OrderBy, int NumOfItemsPerPage, int PageNo, out int NumberOfPages, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {

            NumberOfPages = 0;
            string strSQL = "";
            ds = null;
            // build SQL statement
            strSQL = "Select * from tbl_permissions  where 1=1 ";
            if ((strIDs != "") && (strIDs != null))
            {
                if (strIDs != "All")
                    strSQL += " AND `ID` in (" + strIDs + ")";
            }
            if ((OrderBy != null) && (OrderBy != ""))
                strSQL = strSQL + " Order By " + DBModule.RefineString(OrderBy);
            //GV.Response.Write("sql=" + strSQL);
            // run SQL statement
            if (NumOfItemsPerPage > 0)
            {
                ds = DBModule.ExecuteQuery(strSQL, NumOfItemsPerPage, PageNo, out NumberOfPages, cn, trans);
            }
            else
                ds = DBModule.ExecuteQuery(strSQL, cn, trans);
            //GV.Response.Write(strSQL);
        }

        public void DoSearch(string strKeywords, string OrderBy, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            ds = null;
            // build SQL statement
            strSQL = "Select `ID` from tbl_permissions where 1=1 ";

            if ((OrderBy != null) && (OrderBy != ""))
                strSQL = strSQL + " Order By " + DBModule.RefineString(OrderBy);
            // run SQL statement
            ds = DBModule.ExecuteQuery(strSQL, cn, trans);
        }

        public string GetObjectPermissionsByUserID(string UserID, string strObject, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            DataSet ds = null;
            string reVal = "0";
            strSQL = "SELECT VALUE FROM tbl_permissions WHERE userid=" + UserID + "  AND ObjectID IN (SELECT id FROM tbl_objects WHERE CODE='" + strObject + "')";
            ds = DBModule.ExecuteQuery(strSQL, GV.cn, GV.trans);
            if (ds.Tables[0].Rows.Count > 0)
                reVal = ds.Tables[0].Rows[0]["value"].ToString();
            return reVal;
        }        
        public string GetPermissionIDByUserID(string UserID, string strObject, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            DataSet ds = null;
            string reVal = "";
            strSQL = "SELECT ID FROM tbl_permissions WHERE userid=" + UserID + "  AND ObjectID IN (SELECT id FROM tbl_objects WHERE CODE='" + strObject + "')";
            ds = DBModule.ExecuteQuery(strSQL, GV.cn, GV.trans);
            if (ds.Tables[0].Rows.Count > 0)
                reVal = ds.Tables[0].Rows[0]["ID"].ToString();
            return reVal;
        }
        public int GetObjectIDByObjectCode(string strObjectCode, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            DataSet ds = null;
            int reVal = 0;
            strSQL = "SELECT ID FROM tbl_objects WHERE code='" + strObjectCode + "'";
            ds = DBModule.ExecuteQuery(strSQL, GV.cn, GV.trans);
            if (ds.Tables[0].Rows.Count > 0)
                reVal = int.Parse(ds.Tables[0].Rows[0]["id"].ToString());
            return reVal;
        }
    }
}

